package dao.hibernate;

import dao.common.CategoryManager;
import models.exceptions.CreateEntityException;
import models.exceptions.DeleteEntityException;
import models.shop.Category;
import models.shop.CategoryAlias;
import models.shop.CategoryImageRecord;
import models.shop.Image;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.List;

public class CategoryManagerHB implements CategoryManager {
    public class QueryAndParams{
        public String query;
        public Object[] params;
    }
    public void save(Category category) throws CreateEntityException {
        EntityManager entityManager = Category.em();
        entityManager.clear();
        try {
            long categoryCount = Category.count();
            if (categoryCount == 0) {
                category.leftValue = 1;
                category.rightValue = 2;
                category.save();
            } else {
                if (category.parentCategoryId == 0) {
                    throw new CreateEntityException("ParentCategoryId must be set");
                }
                makeShiftForCreate(category);
                category.save();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new CreateEntityException(ex.getMessage());
        }
    }

    public List<Category> listByParentId(int parentId) {
        return Category.find("byParentCategoryId", parentId).fetch();
    }

    public List<Category> listByParentId(int id, boolean countProducts) {
        if (!countProducts) {
            return listByParentId(id);
        } else {
            return Category.find("select new Category(c.id, c.title, 0) " +
                    "from Category c " +
                    "where parentCategoryId = ?", id).fetch();
        }
    }

    public List<Category> listByParentId(int id, int brandId) {
        Category category = Category.findById(id);
        QueryAndParams queryAndParams = getLisByParentQueryAndParams(id, brandId, category);
        return Category.find(queryAndParams.query, queryAndParams.params).fetch();
    }

    public Category getForEdit(int id) {
        return Category.findById(id);
    }

    public void addAlias(CategoryAlias alias) {
        Category category = Category.findById(alias.categoryId);
        alias.save();
        category.aliases.add(alias);
        category.save();
    }

    public void deleteAlias(int aliasId) {
        CategoryAlias.delete("id = ?", aliasId);
    }

    public void addImage(int categoryId, Image image) {
        Category category = Category.findById(categoryId);
        image.save();
        category.images.add(image);
        category.save();
    }

    public void deleteImage(int categoryId, int imageId) {
        CategoryImageRecord.delete("categoryId = ? and imageId = ?", categoryId, imageId);
        Image.delete("id = ?", imageId);
    }

    public List<CategoryImageRecord> getCategoryImages() {
        return CategoryImageRecord.findAll();
    }

    private QueryAndParams getLisByParentQueryAndParams(int id, int brandId, Category category) {
        QueryAndParams queryAndParams = new QueryAndParams();

        if (brandId > 0) {
            String query = "select new Category(parent.id, parent.title, count(p.id) as pcount)" +
                    " from Product p, Category parent" +
                    " where (parent.leftValue <= p.category.leftValue and " +
                    "  parent.rightValue >= p.category.rightValue " +
                    "  and parent.parentCategoryId = ?) " +
                    "  and (p.category.leftValue > ? and " +
                    "  p.category.rightValue < ?) ";
            query += " and p.brand.id = ? group by parent.id";
            queryAndParams.params = new Object[]{id, category.leftValue, category.rightValue, brandId};
            queryAndParams.query = query;
        }
        else {
            String query = "select new Category(parent.id, parent.title, count(p.id) as pcount)" +
                    " from Product p, Category parent" +
                    " where (parent.leftValue <= p.category.leftValue and " +
                    "  parent.rightValue >= p.category.rightValue " +
                    "  and parent.parentCategoryId = ?) " +
                    "  and (p.category.leftValue > ? and " +
                    "  p.category.rightValue < ?) group by parent.id";
            queryAndParams.params = new Object[]{id, category.leftValue, category.rightValue};
            queryAndParams.query = query;
        }

        return queryAndParams;
    }

    public Category getById(int id) {
        return Category.findById(id);
    }

    public long count() {
        return Category.count();
    }

    public void delete(int id) throws DeleteEntityException {
        EntityManager entityManager = Category.em();
        entityManager.clear();
        try {
            Category category = Category.findById(id);
            int myLeft = category.leftValue;
            int myRight = category.rightValue;
            int myWidth = myRight - myLeft + 1;
            Query deleteQuery = entityManager.createNativeQuery(
                    "delete from categories where leftValue between ? and ?"
            ).setParameter(1, myLeft).setParameter(2, myRight);
            deleteQuery.executeUpdate();
            Query updateRightValueQuery = entityManager.createNativeQuery(
                    "update categories set rightValue = rightValue - ? where rightValue > ?"
            ).setParameter(1, myWidth).setParameter(2, myRight);
            updateRightValueQuery.executeUpdate();
            Query updateLeftValueQuery = entityManager.createNativeQuery(
                    "update categories set leftValue = leftValue - ? where leftValue > ?"
            ).setParameter(1, myWidth).setParameter(2, myRight);
            updateLeftValueQuery.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new DeleteEntityException(ex.getMessage());
        }
    }

    public void clearCache() {
        Category.em().clear();
    }

    public Category create(String title, int parentId) throws CreateEntityException {
        Category category = new Category();
        category.title = title;
        category.parentCategoryId = parentId;
        save(category);
        return category;
    }

    public Category createRoot(String title) throws CreateEntityException {
        return create(title, 0);
    }


    private void makeShiftForCreate(Category category) {
        EntityManager entityManager = Category.em();
        Category parentCategory = Category.findById(category.parentCategoryId);
        int rightValue = parentCategory.rightValue - 1;
        Query updateRightPart = entityManager.createNativeQuery(
                "update categories set rightValue = rightValue + 2 where rightValue > ?;");
        updateRightPart.setParameter(1, rightValue);
        updateRightPart.executeUpdate();
        Query updateLeftPart = entityManager.createNativeQuery(
                "update categories set leftValue = leftValue + 2 where leftValue > ?;");
        updateLeftPart.setParameter(1, rightValue);
        updateLeftPart.executeUpdate();
        category.leftValue = rightValue + 1;
        category.rightValue = rightValue + 2;
    }
}
